﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Text;

/// <summary>
/// Summary description for CustomerDAL
/// </summary>
public class CustomerDAL
{
    public static IList<Customer> GetAll()
    {
        string sqlCommand = @"SELECT     c.CustomerID, c.CustomerCode, c.CustomerName, 
			                            c.Address, c.Phone,
			                            mc.ID AS MonthCardID, mc.PlateNumber, mc.PlateTypeId, 
			                            mc.CarColorId, mc.ParkingFloorId, 
			                            mc.FromDate, mc.ToDate, mc.Price
                            FROM        Customer c 
                            LEFT JOIN   MonthCard mc ON c.CustomerID = mc.CustomerId
                            ORDER BY c.LastUpdate DESC";
        List<Customer> list = new List<Customer>();
        try
        {
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sqlCommand))
            {
                while (dr.Read())
                {
                    Customer customer = new Customer();
                    customer.CustomerID = (Guid)dr["CustomerID"];
                    customer.CustomerCode = SqlHelper.CheckStringNull(dr["CustomerCode"]);
                    customer.CustomerName = SqlHelper.CheckStringNull(dr["CustomerName"]);
                    customer.Address = SqlHelper.CheckStringNull(dr["Address"]);
                    customer.Phone = SqlHelper.CheckStringNull(dr["Phone"]);
                    MonthCard monthCard = null;
                    if (dr["MonthCardID"] != null)
                    {
                        monthCard = new MonthCard();
                        monthCard.ID = SqlHelper.CheckGuidNull(dr["MonthCardID"]);
                        monthCard.PlateNumber = SqlHelper.CheckStringNull(dr["PlateNumber"]);
                    }
                    customer.MonthCard = monthCard;
                    list.Add(customer);
                }
            }
        }
        catch (Exception)
        {
            throw;
        }
        return list;
    }

    public static Customer GetById(Guid id)
    {
        string sqlCommand = @"SELECT     c.CustomerID, c.CustomerCode, c.CustomerName, 
			                            c.Address, c.Phone,
			                            mc.ID AS MonthCardID, mc.PlateNumber, mc.PlateTypeId, 
			                            mc.CarColorId, mc.ParkingFloorId, 
			                            mc.FromDate, mc.ToDate, mc.Price
                            FROM        Customer c 
                            LEFT JOIN   MonthCard mc ON c.CustomerID = mc.CustomerId
                              WHERE c.CustomerID=@CustomerID";
        //parms
        SqlParameter parm = new SqlParameter("@CustomerID", id);
        Customer customer = null;
        try
        {
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sqlCommand, parm))
            {
                while (dr.Read())
                {
                    customer = new Customer();
                    customer.CustomerID = (Guid)dr["CustomerID"];
                    customer.CustomerCode = SqlHelper.CheckStringNull(dr["CustomerCode"]);
                    customer.CustomerName = SqlHelper.CheckStringNull(dr["CustomerName"]);
                    customer.Address = SqlHelper.CheckStringNull(dr["Address"]);
                    customer.Phone = SqlHelper.CheckStringNull(dr["Phone"]);
                    MonthCard monthCard = null;
                    if (dr["MonthCardID"] != null)
                    {
                        monthCard = new MonthCard();
                        monthCard.ID = SqlHelper.CheckGuidNull(dr["MonthCardID"]);
                        monthCard.PlateNumber = SqlHelper.CheckStringNull(dr["PlateNumber"]);
                    }
                    customer.MonthCard = monthCard;
                    break;
                }
            }
        }
        catch (Exception)
        {
            throw;
        }
        return customer;
    }

    public static IList<Customer> GetByCustomer(Customer searchCust)
    {
        string sqlCommand = @"SELECT     c.CustomerID, c.CustomerCode, c.CustomerName, 
			                            c.Address, c.Phone,
			                            mc.ID AS MonthCardID, mc.PlateNumber, mc.PlateTypeId, 
			                            mc.CarColorId, mc.ParkingFloorId, 
			                            mc.FromDate, mc.ToDate, mc.Price
                            FROM        Customer c 
                            LEFT JOIN   MonthCard mc ON c.CustomerID = mc.CustomerId
                            WHERE 1=1";
        if (!String.IsNullOrEmpty(searchCust.CustomerCode))
        {
            sqlCommand += " AND c.CustomerCode LIKE '%' + @CustomerCode + '%'";
        }

        if (!String.IsNullOrEmpty(searchCust.CustomerName))
        {
            sqlCommand += " AND c.CustomerName LIKE '%' + @CustomerName + '%'";
        }

        if (searchCust.MonthCard != null && !String.IsNullOrEmpty(searchCust.MonthCard.PlateNumber))
        {
            sqlCommand += @" AND dbo.ufn_StripCharacters(UPPER(mc.PlateNumber), '^a-z0-9') 
                                LIKE '%' + dbo.ufn_StripCharacters(UPPER(@PlateNumber), '^a-z0-9') + '%'";
        }
        sqlCommand += " ORDER BY c.LastUpdate DESC";
        SqlParameter[] parms = new SqlParameter[]
        {
            new SqlParameter("@CustomerCode",searchCust.CustomerCode),
            new SqlParameter("@CustomerName",searchCust.CustomerName),
            new SqlParameter("@PlateNumber", searchCust.MonthCard!=null?searchCust.MonthCard.PlateNumber:String.Empty)
        };
        List<Customer> list = new List<Customer>();
        try
        {
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sqlCommand,parms))
            {
                while (dr.Read())
                {
                    Customer customer = new Customer();
                    customer.CustomerID = (Guid)dr["CustomerID"];
                    customer.CustomerCode = SqlHelper.CheckStringNull(dr["CustomerCode"]);
                    customer.CustomerName = SqlHelper.CheckStringNull(dr["CustomerName"]);
                    customer.Address = SqlHelper.CheckStringNull(dr["Address"]);
                    customer.Phone = SqlHelper.CheckStringNull(dr["Phone"]);
                    MonthCard monthCard = null;
                    if (dr["MonthCardID"] != null)
                    {
                        monthCard = new MonthCard();
                        monthCard.ID = SqlHelper.CheckGuidNull(dr["MonthCardID"]);
                        monthCard.PlateNumber = SqlHelper.CheckStringNull(dr["PlateNumber"]);
                    }
                    customer.MonthCard = monthCard;
                    list.Add(customer);
                }
            }
        }
        catch (Exception)
        {
            throw;
        }
        return list;
    }


    /// <summary>
    /// Gioinv
    /// </summary>
    /// <param name="searchCust"></param>
    /// <param name="page"></param>
    /// <param name="pageSize"></param>
    /// <returns></returns>
    public static IList<Customer> GetByCustomerPaging(Customer searchCust, int page, int pageSize)
    {
        SqlParameter[] parms = new SqlParameter[]
        {
            new SqlParameter("@Page",page),
            new SqlParameter("@PageSize",pageSize),
            String.IsNullOrEmpty(searchCust.CustomerCode) ? new SqlParameter("@CustomerCode", DBNull.Value) :
                                                            new SqlParameter("@CustomerCode", searchCust.CustomerCode),

            String.IsNullOrEmpty(searchCust.CustomerName) ? new SqlParameter("@CustomerName", DBNull.Value) :
                                                            new SqlParameter("@CustomerName", searchCust.CustomerName),
            
            String.IsNullOrEmpty(searchCust.MonthCard.PlateNumber) ? new SqlParameter("@PlateNumber", DBNull.Value) :
                                                            new SqlParameter("@PlateNumber", searchCust.MonthCard.PlateNumber)
        };
        List<Customer> list = new List<Customer>();
        try
        {
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "[dbo].[CustomerPaging]", parms))
            {
                while (dr.Read())
                {
                    Customer customer = new Customer();
                    customer.CustomerID = (Guid)dr["CustomerID"];
                    customer.CustomerCode = SqlHelper.CheckStringNull(dr["CustomerCode"]);
                    customer.CustomerName = SqlHelper.CheckStringNull(dr["CustomerName"]);
                    customer.Address = SqlHelper.CheckStringNull(dr["Address"]);
                    customer.Phone = SqlHelper.CheckStringNull(dr["Phone"]);
                    MonthCard monthCard = null;
                    if (dr["MonthCardID"] != null)
                    {
                        monthCard = new MonthCard();
                        monthCard.ID = SqlHelper.CheckGuidNull(dr["MonthCardID"]);
                        monthCard.PlateNumber = SqlHelper.CheckStringNull(dr["PlateNumber"]);
                    }
                    customer.MonthCard = monthCard;
                    list.Add(customer);
                }
            }
        }
        catch (Exception)
        {
            throw;
        }
        return list;
    }


    public static int GetByCustomerTotal(Customer searchCust)
    {
        int total = 0;
        SqlParameter[] parms = new SqlParameter[]
        {
            String.IsNullOrEmpty(searchCust.CustomerCode) ? new SqlParameter("@CustomerCode", DBNull.Value) :
                                                            new SqlParameter("@CustomerCode", searchCust.CustomerCode),

            String.IsNullOrEmpty(searchCust.CustomerName) ? new SqlParameter("@CustomerName", DBNull.Value) :
                                                            new SqlParameter("@CustomerName", searchCust.CustomerName),
            
            String.IsNullOrEmpty(searchCust.MonthCard.PlateNumber) ? new SqlParameter("@PlateNumber", DBNull.Value) :
                                                            new SqlParameter("@PlateNumber", searchCust.MonthCard.PlateNumber)
        };
       
        try
        {
            total = (int)SqlHelper.ExecuteScalar(CommandType.StoredProcedure, "[dbo].[CustomerPagingTotal]", parms);
        }
        catch (Exception)
        {
            throw;
        }
        return total;
    }

    public static string GetMaxCusCode()
    {
        string strSQL = @"SELECT [dbo].[ufn_GetCustomerCode]('C',6)";
        try
        {
            return SqlHelper.ExecuteScalar(CommandType.Text, strSQL).ToString();
        }
        catch (Exception)
        {
            throw;
        }
    }

    private static string PrepareSQLInsert(Customer customer, ref SqlParameter[] parms)
    {
        string sql = @"INSERT INTO [Customer]
                       ([CustomerCode]
                       ,[CustomerName]
                       ,[Email]
                       ,[Address]
                       ,[Phone]
                       ,[LastUser]
                       ,[LastUpdate])
                 VALUES
                       (@CustomerCode
                       ,@CustomerName
                       ,@Email
                       ,@Address
                       ,@Phone
                       ,@LastUser
                       ,GETDATE())";
        //parms
        parms = new SqlParameter[]
                    {
                        new SqlParameter("@CustomerCode",customer.CustomerCode),
                        new SqlParameter("@CustomerName",customer.CustomerName),
                        new SqlParameter("@Email",customer.Email),
                        new SqlParameter("@Address",customer.Address),
                        new SqlParameter("@Phone",customer.Phone),
                        new SqlParameter("@LastUser",customer.LastUser.Id)                      
                    };
        return sql;
    }

    private static string PrepareSQLUpdate(Customer customer, ref SqlParameter[] parms)
    {
        string sql = @"UPDATE [Customer]
                       SET [CustomerCode] = @CustomerCode
                          ,[CustomerName] = @CustomerName
                          ,[Email] = @Email
                          ,[Address] = @Address
                          ,[Phone] = @Phone
                          ,[LastUser] = @LastUser
                          ,[LastUpdate] = GETDATE()
                     WHERE [CustomerID] = @CustomerID";
        //parms
        parms = new SqlParameter[]
                    {
                        new SqlParameter("@CustomerID",customer.CustomerID),
                        new SqlParameter("@CustomerCode",customer.CustomerCode),
                        new SqlParameter("@CustomerName",customer.CustomerName),
                        new SqlParameter("@Email",customer.Email),
                        new SqlParameter("@Address",customer.Address),
                        new SqlParameter("@Phone",customer.Phone),
                        new SqlParameter("@LastUser",customer.LastUser.Id)                      
                    };
        return sql;
    }

    public static void Save(Customer customer)
    {
        string sqlCommand = String.Empty;
        SqlParameter[] parms = null;
        if (customer.CustomerID.Equals(Guid.Empty))
        {
            sqlCommand = PrepareSQLInsert(customer, ref parms);
        }
        else
        {
            sqlCommand = PrepareSQLUpdate(customer, ref parms);
        }

        try
        {
            SqlHelper.ExecuteNonQuery(CommandType.Text, sqlCommand, parms);
        }
        catch (Exception)
        {
            throw;
        }

    }

    public static bool CanDelete(Guid id)
    {
        string strSQL = @"SELECT SUM(ID)
                        FROM
                        (
	                        SELECT Count(ID) AS ID FROM MonthCardHistory
	                        WHERE CustomerId=@CustomerId
	                        UNION
	                        SELECT Count(ID) AS ID FROM MonthCard
	                        WHERE CustomerId=@CustomerId
                        )tmp";

        //parms
        SqlParameter parm = new SqlParameter("@CustomerId", id);

        try
        {
            return SqlHelper.CheckIntNull(SqlHelper.ExecuteScalar(CommandType.Text, strSQL, parm).ToString()) == 0;
        }
        catch (Exception)
        {
            throw;
        }
    }

    public static void Delete(Guid id)
    {
        string sqlCommand = "DELETE FROM Customer WHERE CustomerId=@CustomerId";
        //parms
        SqlParameter parm = new SqlParameter("@CustomerId", id);

        try
        {
            SqlHelper.ExecuteNonQuery(CommandType.Text, sqlCommand, parm);
        }
        catch (Exception)
        {
            throw;
        }
    }

    public static bool IsExistedCustomerCode(string code)
    {
        string strSQL = @"SELECT COUNT(CustomerId) FROM Customer 
                            WHERE CustomerCode=@CustomerCode";

        //parms
        SqlParameter parm = new SqlParameter("@CustomerCode", code);

        try
        {
            return SqlHelper.CheckIntNull(SqlHelper.ExecuteScalar(CommandType.Text, strSQL, parm).ToString()) > 0;
        }
        catch (Exception)
        {
            throw;
        }
    }

}